/**   
 * @Title: ExcelWriter.java 
 * @Package com.git.gather 
 * @Description: excel写入相关工具类，支持xls，与slsx
 * @author fanqinghui100@126.com   
 * @date 2014年6月2日 下午8:19:46 
 * @version V1.0   
 */
package com.git.gather;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.git.gather.common.BasePo;
import com.git.gather.common.Common;

public class ExcelWriter {

	Logger logger = Logger.getAnonymousLogger();
	private String title;
	private String[] headers;
	private String[] keys;
	private Collection<?> data;
	private String version;
	// 必填列，控制样式标红
	private Set<String> requiredKeys = new HashSet<String>();
	// 为日期类型字段指定格式的Map
	private Map<String, String> formateDate;

	private Map<String, Method> getterCache;
	private Class<?>[] paramTypes = new Class<?>[] {};
	private Object[] callingArgs = new Object[] {};

	/**
	 * 创建Excel输出类(xlsx)
	 * 
	 * @param title
	 *            sheet的标题
	 * @param headers
	 *            标题行
	 * @param keys
	 *            列对应的field
	 * @param data
	 *            数据
	 */
	public ExcelWriter(String title, String[] headers, String[] keys, Collection<?> data) {
		this(title, headers, keys, data, Common.EXCEL_VERSION_XLSX);
	}

	/**
	 * 创建Excel输出类(指定是xls还是xlsx)
	 * 
	 * @param title
	 *            sheet的标题
	 * @param headers
	 *            标题行
	 * @param keys
	 *            列对应的field
	 * @param data
	 *            数据
	 * @param version
	 *            版本
	 */
	public ExcelWriter(String title, String[] headers, String[] keys, Collection<?> data, String version) {
		this.title = title;
		this.headers = headers;
		this.keys = keys;
		this.data = data;
		this.version = version;
		getterCache = new HashMap<String, Method>();
	}

	/**
	 * 直接返回输入流，结合Struts的StreamResult较方便
	 * 
	 * @return
	 * @throws Exception 
	 */
	public InputStream newInputStream() throws Exception {
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		writeTo(bos);
		return new ByteArrayInputStream(bos.toByteArray());
	}

	/**
	 * 输出到指定的输出流
	 * 
	 * @param os
	 * @throws Exception 
	 */
	public void writeTo(OutputStream os) throws Exception {
		try {
			generateWorkbook().write(os);
		} catch (IOException e) {
			logger.info("导出Excel时出现IO异常");
			throw new RuntimeException(e);
		}
	}

	/**
	 * 返回Workbook对象，便于调用者进行个性化定制
	 * 
	 * @return
	 * @throws Exception 
	 */
	public Workbook generateWorkbook() throws Exception {
		Workbook workbook = newWorkbook();
		Sheet sheet = workbook.createSheet(title);
		sheet.setDefaultColumnWidth(10);

		Font headerFont = workbook.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontName("Arial");
		headerFont.setFontHeightInPoints((short) 10);

		Font requiredHeaderFont = workbook.createFont();
		requiredHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		requiredHeaderFont.setFontName("Arial");
		requiredHeaderFont.setColor(Font.COLOR_RED);
		requiredHeaderFont.setFontHeightInPoints((short) 10);

		Font plainTextFont = workbook.createFont();
		plainTextFont.setFontName("Arial");
		plainTextFont.setFontHeightInPoints((short) 10);

		CellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setFont(headerFont);

		CellStyle requiredHeaderStyle = workbook.createCellStyle();
		requiredHeaderStyle.setFont(requiredHeaderFont);

		CellStyle numStyle = workbook.createCellStyle();
		numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));// (自定义货币)格式
		numStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 右对齐
		numStyle.setFont(plainTextFont);

		CellStyle numNotDotStyle = workbook.createCellStyle();
		numNotDotStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));// (数字类型
		numNotDotStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 右对齐
		numNotDotStyle.setFont(plainTextFont);

		CellStyle textStyle = workbook.createCellStyle();
		textStyle.setFont(plainTextFont);

		// 标题行
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			if (requiredKeys.contains(keys[i]))
				cell.setCellStyle(requiredHeaderStyle);
			else
				cell.setCellStyle(headerStyle);
			cell.setCellValue(headers[i]);
		}

		Iterator<?> it = data.iterator();
		int index = 1;
		while (it.hasNext()) {
			row = sheet.createRow(index);
			try {
				fillRow(row, it.next(), index++, textStyle, numStyle, numNotDotStyle);
			} catch (Exception e) {
				throw new Exception("导出Excel时出现反射调用异常:", e);
			}
		}
		return workbook;
	}

	private Row fillRow(Row row, Object o, int rowIndex, CellStyle cellStyle, CellStyle numCellStyle, CellStyle numNotDotStyle) throws Exception {
		int index = 0;
		for (String key : keys) {
			Cell cell = row.createCell(index++);
			Object fieldValue = "";

			if (o instanceof BasePo) {
				Method method = null;
				if (rowIndex == 1) {
					method = o.getClass().getMethod("get" + Character.toUpperCase(key.charAt(0)) + key.substring(1), paramTypes);
					getterCache.put(key, method);
				} else {
					method = getterCache.get(key);
				}
				fieldValue = method.invoke(o, callingArgs);
			} else if (o instanceof Map) {
				fieldValue = ((Map<?, ?>) o).get(key);
			}

			cell.setCellStyle(cellStyle);
			if (fieldValue == null) {
				cell.setCellValue("");
			} else {
				if (fieldValue instanceof Number) {// 数字
					if (fieldValue instanceof BigDecimal || fieldValue instanceof Double || fieldValue instanceof Float) {
						cell.setCellStyle(numCellStyle);
					} else if (fieldValue instanceof BigInteger || fieldValue instanceof Integer) {
						cell.setCellStyle(numNotDotStyle);
					}
					cell.setCellValue(Double.valueOf(fieldValue.toString()));
				} else if (fieldValue instanceof Date) {// 日期
					if (formateDate != null && formateDate.get(key) != null) {
						cell.setCellValue(DateUtils.formatDate((Date) fieldValue, formateDate.get(key)));
					} else {
						cell.setCellValue(DateUtils.formatDate((Date) fieldValue, DateUtils.DATE_SMALL_STR));
					}
				} else {// 其他为字符串
					if (StringUtils.isNotEmpty(fieldValue.toString()) && formateDate != null && formateDate.containsKey(key)) {
						try {
							Date date =DateUtils.parseDate(fieldValue.toString(), Common.PARSEPATTERNS);
							cell.setCellValue(DateUtils.formatDate(date, formateDate.get(key)));
						} catch (ParseException e) {
							logger.info("无法将数据转换为日期格式：" + fieldValue.toString());
							cell.setCellValue(fieldValue.toString());
						}
					} else {
						cell.setCellValue(fieldValue.toString());
					}
				}
			}
		}
		return row;
	}

	private Workbook newWorkbook() {
		if(version.equals(Common.EXCEL_VERSION_XLS)){
			return new HSSFWorkbook();
		}else{
			return new SXSSFWorkbook();//SXSSFWorkbook更适合大数据量excel写入操作
		}
	}

	public void setRequiredKeys(Set<String> requiredKeys) {
		this.requiredKeys = requiredKeys;
	}

	public void setRequiredKeys(String... keys) {
		Collections.addAll(requiredKeys, keys);
	}

	public void setFormateDate(Map<String, String> formateDate) {
		this.formateDate = formateDate;
	}

}
